package com.study.dao.impl;

import com.study.dao.AthletesInfoDAO;
import com.study.dto.AthletesDto;
import com.study.pojo.AthletesInfo;
import com.study.util.JdbcUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @PackgeName: com.study.dao.impl
 * @ClassName: AthletesInfoDAOImpl
 * @Author: 严志祥
 * Date: 2022-05-04 18:55
 * project name: web_project_medal
 * @Version:
 * @Description:
 */
public class AthletesInfoDAOImpl implements AthletesInfoDAO {
	private final static Logger log = LoggerFactory.getLogger(AthletesInfoDAOImpl.class);
    Connection conn = JdbcUtil.getConn();
    @Override
    public int insert(AthletesDto entity) throws SQLException {
        String sql = "insert into athletes_info (country_code,athletes_name,athletes_birthdate,athletes_height," +
                "athletes_weight,athletes_img,athletes_brief,athletes_sex) values (?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,entity.getCountryCode());
        pstmt.setObject(2,entity.getAthletesName());
        pstmt.setObject(3,entity.getAthletesBirthdate());
        pstmt.setObject(4,entity.getAthletesHeight());
        pstmt.setObject(5,entity.getAthletesWeight());
        pstmt.setObject(6,entity.getAthletesImg());
        pstmt.setObject(7,entity.getAthletesBrief());
        pstmt.setObject(8,entity.getAthletesSex());
        log.info(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public int updateById(AthletesDto entity) throws SQLException {
        String sql = "update athletes_info set country_code=?,athletes_name=?,athletes_birthdate=?,athletes_height=?," +
                "athletes_weight=?,athletes_img=?,athletes_brief=?,athletes_sex=? where athletes_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,entity.getCountryCode());
        pstmt.setObject(2,entity.getAthletesName());
        pstmt.setObject(3,entity.getAthletesBirthdate());
        pstmt.setObject(4,entity.getAthletesHeight());
        pstmt.setObject(5,entity.getAthletesWeight());
        pstmt.setObject(6,entity.getAthletesImg());
        pstmt.setObject(7,entity.getAthletesBrief());
        pstmt.setObject(8,entity.getAthletesSex());
        pstmt.setObject(9,entity.getAthletesId());
        log.info(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from athletes_info where athletes_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        log.info(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public AthletesDto selectById(Serializable id) throws SQLException {
        String sql = "select * from athletes_info where athletes_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        ResultSet rs = pstmt.executeQuery();
        AthletesDto athletesInfo = new AthletesDto();
        if (rs.next()){
            athletesInfo.setAthletesId(rs.getInt("athletes_id"));
            athletesInfo.setCountryCode(rs.getString("country_code"));
            athletesInfo.setAthletesName(rs.getString("athletes_name"));
            athletesInfo.setAthletesBirthdate(rs.getDate("athletes_birthdate"));
            athletesInfo.setAthletesHeight(rs.getInt("athletes_height"));
            athletesInfo.setAthletesWeight(rs.getDouble("athletes_weight"));
            athletesInfo.setAthletesImg(rs.getString("athletes_img"));
            athletesInfo.setAthletesBrief(rs.getString("athletes_brief"));
            athletesInfo.setAthletesSex(rs.getInt("athletes_sex"));
        }
        log.info(sql);
        return athletesInfo;
    }

    @Override
    public List<AthletesDto> select(int index, int len) throws SQLException {
        String sql = "select * from athletes_info a " +
                "inner join country_info c " +
                "on a.country_code= c.country_code  " +
                "order by athletes_id desc limit ?,?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,index);
        pstmt.setObject(2,len);
        ResultSet rs = pstmt.executeQuery();
        List<AthletesDto> list = new ArrayList<>();
        while(rs.next()){
            AthletesDto dto = new AthletesDto();
            dto.setAthletesId(rs.getInt("athletes_id"));
            dto.setCountryCode(rs.getString("country_code"));
            dto.setAthletesName(rs.getString("athletes_name"));
            dto.setAthletesBirthdate(rs.getDate("athletes_birthdate"));
            dto.setAthletesHeight(rs.getInt("athletes_height"));
            dto.setAthletesWeight(rs.getDouble("athletes_weight"));
            dto.setAthletesImg(rs.getString("athletes_img"));
            dto.setAthletesBrief(rs.getString("athletes_brief"));
            dto.setAthletesSex(rs.getInt("athletes_sex"));
            dto.setCountryName(rs.getString("country_name"));
            list.add(dto);
        }
        log.info(sql);
        return list;
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from athletes_info";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        int rsInt = rs.getInt("count(*)");
        log.info(sql);
        return rsInt;
    }

    @Override
    public List<AthletesDto> selectAthletes(String athletesName) throws SQLException {
        String sql = "select\n" +
                "tb1.*,\n" +
                "sp.sporting_name\n" +
                "from \n" +
                "sporting_events sp\n" +
                "inner join \n" +
                "(select\n" +
                "a.athletes_name,\n" +
                "a.athletes_img,\n" +
                "c.country_name,\n" +
                "a.athletes_sex,\n" +
                "a.athletes_birthdate,\n" +
                "a.athletes_height,\n" +
                "a.athletes_weight,\n" +
                "a.athletes_brief,\n" +
                "aw.award_date,\n" +
                "aw.sporting_id,\n" +
                "aw.award_ranking\n" +
                "from \n" +
                "athletes_info a inner join country_info c \n" +
                "on a.country_code = c.country_code\n" +
                "inner join \n" +
                "award_info aw\n" +
                "on aw.athletes_id = a.athletes_id)tb1\n" +
                "on sp.sporting_id = tb1.sporting_id " +
                "where athletes_name = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,athletesName);
        ResultSet rs = pstmt.executeQuery();
        List<AthletesDto> list = new ArrayList<>();
        while(rs.next()){
            AthletesDto a = new AthletesDto();
            a.setAthletesName(rs.getString("athletes_name"));
            a.setAthletesImg(rs.getString("athletes_img"));
            a.setAthletesSex(rs.getInt("athletes_sex"));
            a.setAthletesBirthdate(rs.getDate("athletes_birthdate"));
            a.setAthletesHeight(rs.getInt("athletes_height"));
            a.setAthletesWeight(rs.getDouble("athletes_weight"));
            a.setAthletesBrief(rs.getString("athletes_brief"));
            a.setAwardDate(rs.getDate("award_date"));
            a.setSportingName(rs.getString("sporting_name"));
            a.setAwardRanking(rs.getInt("award_ranking"));
            a.setCountryName(rs.getString("country_name"));
            list.add(a);
        }
        log.info(sql);
        return list;
    }

    @Override
    public boolean selectByAthletesName(String athletesName) throws SQLException {
        String sql = "select * from athletes_info where athletes_name = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,athletesName);
        ResultSet rs = pstmt.executeQuery();
        log.info(sql);
        if(rs.next()){
            return true;
        }else {
            return false;
        }
    }
}
